﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using System.Data;

namespace DataAccess
{
    public class BestRoutesData
    {
        public bool BestRouteAdd(int crCode, int gid, string lons, string lats, string alts, string field, double total, int exitId, int courseID)
        {
            Database database = DatabaseFactory.CreateDatabase();
            string sqlCommand = "BestRouteAdd";
            DbCommand dbCommand = database.GetStoredProcCommand(sqlCommand);
            database.AddOutParameter(dbCommand, "@AffectRows", DbType.Int32, 10);
            database.AddInParameter(dbCommand, "@CRcode", DbType.Int32, crCode);
            database.AddInParameter(dbCommand, "@Lons", DbType.AnsiString, lons);
            database.AddInParameter(dbCommand, "@Lats", DbType.AnsiString, lats);
            database.AddInParameter(dbCommand, "@Alts", DbType.AnsiString, alts);
            database.AddInParameter(dbCommand, "@CostField", DbType.AnsiString, field);
            database.AddInParameter(dbCommand, "@TotalCost", DbType.Double, total);
            database.AddInParameter(dbCommand, "@GID", DbType.Int32, gid);
            database.AddInParameter(dbCommand, "@ExitID", DbType.Int32, exitId);
            database.AddInParameter(dbCommand, "@CourseID", DbType.Int32, courseID);
            database.ExecuteDataSet(dbCommand);
            int returnValue = int.Parse(database.GetParameterValue(dbCommand, "@AffectRows").ToString());
            return returnValue > 0 ? true : false;
        }
        public DataTable BestRouteGetByCode(int crCode,int groupId)
        {
            DataTable dt=new DataTable();
            Database database = DatabaseFactory.CreateDatabase();
            string sqlCommand = "BestRouteGetByCode";
            DbCommand dbCommand = database.GetStoredProcCommand(sqlCommand);
            database.AddOutParameter(dbCommand, "@AffectRows", DbType.Int32, 10);
            database.AddInParameter(dbCommand, "@CRcode", DbType.Int32, crCode);
            database.AddInParameter(dbCommand, "@GroupID", DbType.Int32, groupId);
            dt=database.ExecuteDataSet(dbCommand).Tables[0];
            return dt;
        }
        public bool UpdateRouteStatus(int crCode, int groupID)
        {
            Database database = DatabaseFactory.CreateDatabase();
            string sqlCommand = "BestRouteStatusUpdate";
            DbCommand dbCommand = database.GetStoredProcCommand(sqlCommand);
            database.AddOutParameter(dbCommand, "@AffectRows", DbType.Int32, 10);
            database.AddInParameter(dbCommand, "@CrCode", DbType.Int32, crCode);
            database.AddInParameter(dbCommand, "@GroupID", DbType.Int32, groupID);
            database.AddInParameter(dbCommand, "@Status", DbType.Int32, 1);
            database.ExecuteDataSet(dbCommand);
            int returnValue = int.Parse(database.GetParameterValue(dbCommand, "@AffectRows").ToString());
            return returnValue > 0 ? true : false;
        }
        public DataTable BestRouteGetByCodeAndStatus(int crCode, int groupId)
        {
            DataTable dt = new DataTable();
            Database database = DatabaseFactory.CreateDatabase();
            string sqlCommand = "BestRouteNew";
            DbCommand dbCommand = database.GetStoredProcCommand(sqlCommand);
            database.AddOutParameter(dbCommand, "@AffectRows", DbType.Int32, 10);
            database.AddInParameter(dbCommand, "@CRcode", DbType.Int32, crCode);
            database.AddInParameter(dbCommand, "@GroupID", DbType.Int32, groupId);
            dt = database.ExecuteDataSet(dbCommand).Tables[0];
            return dt;
        }
        public DataTable FindRouteHistory(int crCode,DateTime start,DateTime end)
        {
            DataTable dt = new DataTable();
            Database database = DatabaseFactory.CreateDatabase();
            string sqlCommand = "BestRouteFindHistory";
            DbCommand dbCommand = database.GetStoredProcCommand(sqlCommand);
            database.AddOutParameter(dbCommand, "@AffectRows", DbType.Int32, 10);
            database.AddInParameter(dbCommand, "@CRCode", DbType.Int32, crCode);
            database.AddInParameter(dbCommand, "@StartTime", DbType.DateTime, start);
            database.AddInParameter(dbCommand, "@EndTime", DbType.DateTime, end);
            dt = database.ExecuteDataSet(dbCommand).Tables[0];
            return dt;
        }
        public DataTable BestRouteGetByGroupId(int groupId, int status)
        {
            
            DataTable dt = new DataTable();
            Database database = DatabaseFactory.CreateDatabase();
            string sqlCommand = "BestRouteGetByGID";
            DbCommand dbCommand = database.GetStoredProcCommand(sqlCommand);
            database.AddOutParameter(dbCommand, "@AffectRows", DbType.Int32, 10);
            database.AddInParameter(dbCommand, "@Status", DbType.Int32, status);
            database.AddInParameter(dbCommand, "@GroupID", DbType.Int32, groupId);
            dt = database.ExecuteDataSet(dbCommand).Tables[0];
            
            return dt;
        }
    }
}
